﻿using WHLRDF.ORM;
using WHLRDF.Application.BLL;
using WHLRDF.Code.Model;
using WHLRDF.Application.Model;
using Newtonsoft.Json;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Mono.TextTemplating;
using NPOI.SS.Formula.Functions;

namespace WHLRDF.Code.BLL
{
    public partial class DbCodeService : SerivceBase, IDbCodeService
    {


    
    /// <summary>
    /// 系统定义字段
    /// </summary>
    public string[] SystemColumnNames {
            get {
                return new string[] { "createby", "createdate", "lastmodifyuserid", "lastmodifydate", "isdeleted" };
            }
        }

        /// <summary>
        /// 验证数据库是否存在
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool IsDbServerExist(DbServerEntity entity)
        {
            var query = Expression.And(Expression.Eq("IsDeleted", false)
                , Expression.And(Expression.Eq("DbServerName", entity.DbServerName),
                Expression.Not(Expression.Eq("DbServerId", entity.DbServerId))));
            var t = this.Query<DbServerEntity>(query).FirstOrDefault();
            if (t != null)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 验证表是否存在
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool IsDbTableExist(DbTableEntity entity)
        {
            var query = Expression.And(
                Expression.And(Expression.Eq("DbServerId", entity.DbServerId),
                Expression.Eq("IsDeleted", false))
                , Expression.And(Expression.Eq("DbTableName", entity.DbTableName),
                Expression.Not(Expression.Eq("DbTableId", entity.DbTableId))));
            var t = this.Query<DbTableEntity>(query).FirstOrDefault();
            if (t != null)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// 保存表信息
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual bool DbTableSave(DbTableEntity entity, ref string strError)
        {
            if (IsDbTableExist(entity))
            {
                strError = "表已存在，请不要重复添加！";
                return false;
            }

            return this.SaveOrUpdate<DbTableEntity>(entity);

        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="KeyId"></param>
        /// <param name="dbServerType"></param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public bool DbDelete(string KeyId, string dbServerType, ref string strError)
        {

            if (!string.IsNullOrEmpty(KeyId))
            {
                if (dbServerType.Equals("0"))
                {
                    this.Delete<DbServerEntity>(KeyId);
                }
                else if (dbServerType.Equals("2"))
                {
                    this.Delete<DbTableEntity>(KeyId);

                }
                // mSessionFactory.Flush();
            }
            return true;
        }

        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public virtual DbTableEntity GetTableById(string id)
        {

            return this.GetById<DbTableEntity>(id);
        }

        /// <summary>
        /// 数据同步
        /// </summary>
        /// <param name="model"></param>
        /// <param name="ligerGrid"></param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public TaskModel syncTableData(TargetDbServerModel model, LigerGrid ligerGrid)
        {
            TaskModel taskModel = new TaskModel() { 
             IsSuccess=false,
             IsOpen=false,
             Message=""
            };
            if (model.SourceServerId == model.TargetServerId)
            {
                taskModel.Message = "源服务器与目标服务器是同一个，无法更新";
                return taskModel;
            }
            var sourceTable = this.GetById<DbTableEntity>(model.SourceTableId);
            if (sourceTable == null)
            {
                taskModel.Message = "表名不存在";
                return taskModel;
            }
            var sourceServer = this.GetById<DbServerEntity>(sourceTable.DbServerId);
            if (sourceServer == null )
            {
                taskModel.Message = "源数据库服务器不存在！";
                return taskModel;
            }
            var targetServer = this.GetById<DbServerEntity>(model.TargetServerId);
            if (targetServer == null)
            {
                taskModel.Message = "数据库目标服务器不存在！";
                return taskModel;
            }
            LigerGrid targetligerGrid = JSONHelper.FromJson<LigerGrid>(JSONHelper.ToJson(ligerGrid));

            var targetTable = this.Select<DbTableEntity>(
                Expression.And(Expression.Eq(DbTableEntity.__IsDeleted, false),
                Expression.And(Expression.Eq(DbTableEntity.__DbServerId, targetServer.DbServerId),
                Expression.Eq(DbTableEntity.__DbTableName, !string.IsNullOrWhiteSpace(model.TargetTableName) ? model.TargetTableName : sourceTable.DbTableName))));
            if (targetTable == null)
            {
                taskModel.Message = "目标服务器中不存在该表，请先创建或者同步表！";
                return taskModel;
            }
            ICriterion criter = Expression.And(
                  Expression.Eq(DbColumnEntity.__DbTableId, model.SourceTableId),

                 Expression.Eq(DbTableEntity.__IsDeleted, false));
            var lstColumn = this.Query<DbColumnEntity>(criter, Order.Asc(DbColumnEntity.__OrderNo)).ToList();
            if (lstColumn == null || lstColumn.Count <= 0)
            {
                taskModel.Message = "请先同步字段！";
                return taskModel;
            }

            var sourceHelper = this.CreateRepository(sourceServer.ProviderType, sourceServer.ConntionString);

            ligerGrid.pageIndex = 0;
            ligerGrid = sourceHelper.GetSource(sourceTable.DbTableName, ligerGrid);

            var sourceData = (List<Dictionary<string, object>>)ligerGrid.Rows;
            if (sourceData == null || sourceData.Count <= 0)
            {
                taskModel.Message = "暂无需要同步的数据";
                return taskModel;
            }
            var targetHelper = this.CreateRepository(targetServer.ProviderType, targetServer.ConntionString);
            if (targetTable == null || !targetTable.IsTable)
            {
                taskModel.Message = "目标表不存在或者不是表，无法同步";
                return taskModel;
            }
            ICriterion targetColumnsWhere = Expression.And(
                 Expression.Eq(DbColumnEntity.__DbTableId, targetTable.DbTableId),
                Expression.Eq(DbColumnEntity.__IsDeleted, false));
            var targetColumns = this.Query<DbColumnEntity>(targetColumnsWhere);
            if (targetColumns == null || targetColumns.Count <= 0)
            {
                taskModel.Message = "目标表表结构错误，无法导入";
                return taskModel;
            }
            var primaryKey = targetColumns.Where(x => x.IsPrimary).FirstOrDefault();
            if (primaryKey == null)
            {
                taskModel.Message = "该表未设置主键，请先设置主键";
                return taskModel;
            }
            if (sourceTable.IsTable)
            {
                targetligerGrid = targetHelper.GetSource(targetTable.DbTableName, targetligerGrid);
            }
            else
            {
                targetligerGrid = new LigerGrid();
                targetligerGrid.pageIndex = 0;
                targetligerGrid = targetHelper.GetSource(targetTable.DbTableName, targetligerGrid);
            }
            taskModel=TaskHelper.Instance.TaskStart(targetTable.DbTableName + "表数据同步,同步记录" + sourceData.Count + "行",
                ApplicationEnvironments.DefaultSession.UserId,
                (task) =>
            {
                var targetData = (List<Dictionary<string, object>>)targetligerGrid.Rows;
                if (targetData != null && targetData.Count > 0)
                {
                    sourceData = sourceData.Where(x => targetData.Where(y => x[primaryKey.ColumnName].ToString().ToLower().Equals(y[primaryKey.ColumnName].ToString().ToLower())).FirstOrDefault() == null).ToList();
                }
                if (sourceData != null && sourceData.Count > 0)
                {
                    List<TableSyncDomain> syncDomains = new List<TableSyncDomain>();
                    foreach (var item in sourceData)
                    {
                        if (task.TaskToken.Token.IsCancellationRequested)
                        {
                            return true;
                        }
                        TableSyncDomain syncDomain = new TableSyncDomain();
                        DataParameterCollection dbParameters = new DataParameterCollection(targetHelper);
                        Dictionary<string, object> itemValue = new Dictionary<string, object>();
                        foreach (var keyValue in item)
                        {
                            if (targetColumns.Where(y => keyValue.Key == y.ColumnName).FirstOrDefault() != null)
                            {
                                itemValue.Add(keyValue.Key, keyValue.Value);
                            }
                        }
                        syncDomain.hql = GetInsert(targetHelper, targetTable.DbTableName, itemValue, ref dbParameters);
                        syncDomain.dbParameters = dbParameters;
                        syncDomains.Add(syncDomain);
                    }

                    using (var tran = targetHelper.Begin())
                    {
                        try
                        {
                            if (primaryKey.IsIdentifier)
                            {
                                targetHelper.Identity_Disabled(targetTable.DbTableName);
                            }
                            foreach (var syncDomain in syncDomains)
                            {
                                targetHelper.Execute(syncDomain.hql, syncDomain.dbParameters);
                                if (task.TaskToken.Token.IsCancellationRequested)
                                {
                                    targetHelper.Rollback();
                                    return true;
                                }
                            }
                            if (primaryKey.IsIdentifier)
                            {
                                targetHelper.Identity_Enable(targetTable.DbTableName);
                            }
                            targetHelper.Commit();
                        }
                        catch (Exception ex)
                        {
                            targetHelper.Rollback();
                            throw ex;
                        }

                    }
                }

                return true;
            }, "", true);


            return taskModel;
        }

        /// <summary>
        /// 同步表结构
        /// </summary>
        /// <param name="model"></param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public bool syncTableStruct(TargetDbServerModel model, ref string strError)
        {
            if (string.IsNullOrWhiteSpace(model.SourceServerId)|| string.IsNullOrWhiteSpace(model.SourceTableId))
            {
                strError = "目标表不存在";
                return false;
            }
            if (model.SourceServerId == model.TargetServerId)
            {
                strError = "源服务器与目标服务器是同一个，无法更新";
                return false;
            }
            if (string.IsNullOrWhiteSpace(model.TargetServerId))
            {
                strError = "目标服务器不存在";
                return false;
            }
            var sourceTable = this.GetById<DbTableEntity>(model.SourceTableId);
            if (sourceTable == null)
            {
                strError = "源表不存在或者参数有误";
                return false;
            }
            var sourceServer = this.GetById<DbServerEntity>(model.SourceServerId);
            if (sourceServer == null)
            {
                strError = "数据库源服务器不存在或已删除！";
                return false;
            }
            if (!string.IsNullOrWhiteSpace(sourceServer.ParentId))
            {
                strError = "非主数据库服务器无法同步数据";
                return false;
            }
            var targetServer = this.GetById<DbServerEntity>(model.TargetServerId);
            if (targetServer == null )
            {
                strError = "数据库目标服务器不存在！";
                return false;
            }
            ICriterion criter = Expression.And(
               Expression.Eq("DbTableId", model.SourceTableId),
              Expression.Eq("IsDeleted", false));
            var sourceColumns = this.Query<DbColumnEntity>(criter, Order.Asc(DbColumnEntity.__OrderNo)).ToList();
            if (sourceColumns == null || sourceColumns.Count <= 0)
            {
                strError = "请先同步字段！";
                return false;
            }
            var primaryKey = sourceColumns.Where(x => x.IsPrimary).FirstOrDefault();
            if (primaryKey == null)
            {
                strError = "该表未设置主键，请先设置主键";
                return false;
            }
            var targetTable = this.Select<DbTableEntity>(
                Expression.And(Expression.Eq(DbTableEntity.__IsDeleted, false),
                Expression.And(Expression.Eq(DbTableEntity.__DbServerId, targetServer.DbServerId),
                Expression.Eq(DbTableEntity.__DbTableName, !string.IsNullOrWhiteSpace(model.TargetTableName)?model.TargetTableName:sourceTable.DbTableName))));
            StringBuilder sqlEditer = new StringBuilder();
            var targetHelper = this.CreateRepository(targetServer.ProviderType, targetServer.ConntionString);
            if (targetTable == null)//不存在创建
            {

                List<DbColumnAttribute> dbColumns = (from x in sourceColumns
                select new DbColumnAttribute
                {
                    Name = x.ColumnName,
                    IsPrimaryKey = x.IsPrimary,
                    AllowDBNull = x.AllowDBNull,
                    Default = x.DefaultValue,
                    ColumnType = x.DbTypeName,
                    Identifier = x.IsIdentifier,
                    MaxLength = x.MaxLength,
                    Max = x.Max,
                    SysFieldTypeId=x.SysFieldTypeId,
                    Scale=x.Scale
                }).ToList();
                sqlEditer.Append(targetHelper.CreateTable(sourceTable.DbTableName, dbColumns));
                //strError = "目标服务器中不存在该表，请先创建或者同步表！";
                //return false;
            }
            else
            {
                criter = Expression.And(
               Expression.Eq("DbTableId", targetTable.DbTableId),
              Expression.Eq("IsDeleted", false));
                var targetColumns = this.Query<DbColumnEntity>(criter, Order.Asc(DbColumnEntity.__OrderNo)).ToList();
                if (targetColumns == null || targetColumns.Count <= 0)
                {
                    strError = "服务器表已存在，请先同步该表！";
                    return false;
                }
                var noColumns = sourceColumns.Where(x => (targetColumns.Where(y => x.ColumnName.Equals(y.ColumnName)).FirstOrDefault() == null)).ToList();
                if (noColumns != null && noColumns.Count > 0)
                {
                    foreach (var column in noColumns)
                    {
                        sqlEditer.Append(targetHelper.AddColumn(sourceTable.DbTableName, new DbColumnAttribute
                        {
                            Name = column.ColumnName,
                            IsPrimaryKey = column.IsPrimary,
                            AllowDBNull = column.AllowDBNull,
                            Default = column.DefaultValue,
                            ColumnType = column.DbTypeName,
                            Identifier = column.IsIdentifier,
                            MaxLength = column.MaxLength,
                            Max = column.Max
                        }));
                    }
                }
            }
            if (string.IsNullOrWhiteSpace(sqlEditer.ToString()))
            {
                strError = "暂无需要更新的属性";
                return false;
            }
        
            using (var tran = targetHelper.Begin())
            {
                try
                {
                    targetHelper.Execute(sqlEditer.ToString());
                    targetHelper.Commit();
                }
                catch (Exception ex)
                {
                    strError = ex.Message;
                    targetHelper.Rollback();
                    return false;
                }
            }
            return true;
        }

        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbTypeName">字段类型</param>
        /// <param name="value"></param>
        /// <returns></returns>
        public object GetValue(DbColumnEntity dbColumn, string dbTypeName, string value)
        {
            object itemValue = null;
            if (string.IsNullOrWhiteSpace(value))
            {
                return itemValue;
            }
            if (!string.IsNullOrWhiteSpace(dbColumn.Remark) && dbColumn.Remark.ToLower().Equals("b2cpassword"))
            {
                return MD5Helper.SHAEncrypt(value);
            }
            switch (dbTypeName)
            {
                case "long":
                    itemValue = Convert.ToInt64(value);
                    break;
                case "int":
                    itemValue = Convert.ToInt32(value);
                    break;
                case "float":
                case "double":
                case "decimal":
                    itemValue = Convert.ToDecimal(value);
                    break;
                case "dateTime":
                    itemValue = Convert.ToDateTime(value);
                    break;
                case "single":
                    itemValue = Convert.ToSingle(value);
                    break;
                case "bit":
                    itemValue = (value.ToLower() == "true" || value == "1");
                    break;
                default:
                    itemValue = value;
                    break;
            }
            return itemValue;

        }

     
        
     
    }
}
